import pymysql

source_db_config = {
    'host': 'localhost',
    'user': 'root',
    'password': '123456',
    'database': 'lims'
}

target_db_config = {
    'host': 'localhost',
    'user': 'root',
    'password': '123456',
    'database': 'hello'
}

# 创建数据库连接
src_conn = pymysql.connect(**source_db_config)
tgt_conn = pymysql.connect(**target_db_config)


def get_common_columns(src_cursor, tgt_cursor, table):
    # 获取源表字段
    src_cursor.execute(f"SHOW COLUMNS FROM test.{table};")
    src_columns = {f"`{row[0]}`" for row in src_cursor.fetchall()}  # 用反引号包围字段名

    # 获取目标表字段
    tgt_cursor.execute(f"SHOW COLUMNS FROM hello.{table};")
    tgt_columns = {f"`{row[0]}`" for row in tgt_cursor.fetchall()}

    # 获取公共字段
    common_columns = src_columns.intersection(tgt_columns)
    return list(common_columns)

try:
    with src_conn.cursor() as src_cursor, tgt_conn.cursor() as tgt_cursor:
        # 获取源数据库中的所有表名
        src_cursor.execute("SHOW TABLES;")
        tables = [row[0] for row in src_cursor.fetchall()]

        tgt_cursor.execute("SET foreign_key_checks = 0;")  # 禁用外键检查

        for table in tables:
            try:
                common_columns = get_common_columns(src_cursor, tgt_cursor, table)
                if not common_columns:
                    print(f"Warning: No common columns found for table `{table}`, skipping.")
                    continue
            except pymysql.err.ProgrammingError as e:
                print(f"Programming error for table `{table}`: {e}\n")
            except pymysql.err.IntegrityError as e:
                print(f"Integrity error for table `{table}`: {e}\n")
            except Exception as e:
                print(f"Unexpected error for table `{table}`: {e}\n")
        # 提交更改
        tgt_conn.commit()
finally:
    src_conn.close()
    tgt_conn.close()
